﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace HousingDistribution
{
    class ExcelCreator
    {
        public ExcelCreator(string path, FormHousingDis.UpdateProgressBarDelegate updateFunc)
        {
            this.path = path;

            this.updateFunc = updateFunc;
        }

        public void ExportTOExcel(PersonTypeEnum personType, string title, DataGridView dataGridView)
        {
            if (dataGridView.Rows.Count == 0)
            {
                MessageBox.Show("没有数据可供导出！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }

            if (path == null || path.Length == 0)
                return;

            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Application.Workbooks.Add(true); ;
            excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。 
            if (excel == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Exce!");
                return;
            }
            
            Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
            Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
            Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;

            PrintSet(sheet, excel);

            sheet.Name = "分房结果";
                        
            int excelRowIndex = 1;
            int excelColIndex = 0;

            excelRowIndex++;

            //生成Excel中列头名称  
            for (int i = 0; i < dataGridView.Columns.Count; i++)
            {
                if (dataGridView.Columns[i].Visible == false)
                    continue;

                excelColIndex++;
                excel.Cells[excelRowIndex, excelColIndex] = dataGridView.Columns[i].HeaderText;
                excel.Cells[excelRowIndex, excelColIndex].ColumnWidth = DataDictionary.Instance().GetRptColWidth(personType, dataGridView.Columns[i].DataPropertyName);
            }

            //列数
            int colCount = excelColIndex;

            SetTitle(sheet, title, colCount);

            //填充数据
            for (int i = 0; i < dataGridView.Rows.Count; i++)
            {
                excelColIndex = 0;
                excelRowIndex++;

                for (int j = 0; j < dataGridView.Columns.Count; j++)
                {
                    if (dataGridView.Columns[j].Visible == false)
                        continue;

                    excelColIndex++;

                    Object o = dataGridView[j, i].Value;
                    Type type = dataGridView[j, i].ValueType;

                    if (o != null)
                    {
                        if (type == typeof(string))
                        {
                            excel.Cells[excelRowIndex, excelColIndex] = "'" + dataGridView[j, i].Value.ToString();
                        }
                        else if (type == typeof(DateTime))
                        {
                            try
                            {
                                excel.Cells[excelRowIndex, excelColIndex] = "'" + FormatDate((DateTime)o);
                            }
                            catch (Exception)
                            {
                                excel.Cells[excelRowIndex, excelColIndex] = null;
                            }
                        }
                        else
                        {
                            excel.Cells[excelRowIndex, excelColIndex] = "'" + dataGridView[j, i].Value.ToString();
                        }
                    }
                    else
                    {
                        excel.Cells[excelRowIndex, excelColIndex] = null;
                    }    
                }

                this.updateFunc(i);
            }

            //数据区域格式设置
            //Range range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[sheet.UsedRange.Rows.Count, sheet.UsedRange.Columns.Count]];
            Range range = sheet.Range[sheet.Cells[2, 1], sheet.Cells[sheet.UsedRange.Rows.Count, sheet.UsedRange.Columns.Count]];

            range.WrapText = true; //自动换行
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //中间对齐
            range.EntireRow.AutoFit();  //行高自适应
            range.Borders.LineStyle = 1;    //边框

            range.Font.Size = 10;   //字大小
            range.RowHeight = 25;   //行高

            //sheet.Cells[1, 1].Font.Size = 20;   //标题字体
            //sheet.Cells[1,1].RowHeight = 40;    //标题行高

            sheet.SaveAs(path, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
            book.Close(false, miss, miss);
            books.Close();
            excel.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

            GC.Collect();
        }

        string FormatDate(DateTime date)
        {
            StringBuilder str = new StringBuilder();
            str.Append(date.Year.ToString());

            str.Append(".");
            if (date.Month < 10)
            {
                str.Append("0");
            }
            str.Append(date.Month.ToString());

            return str.ToString();
        }

        //设置报表头
        public void SetTitle(Worksheet sheet, string title, int colCount)   
        {
            string firstColTitle = excelColTitle[0] + "1";

            string lastColTitle;
            if (colCount <= excelColTitle.Length)
                lastColTitle = excelColTitle[colCount - 1] + "1";
            else
                lastColTitle = excelColTitle[excelColTitle.Length - 1] + "1";

            Range range = sheet.get_Range(firstColTitle, lastColTitle); // 获取多个单元格   
            range.Merge();                                              // 合并单元格   
            range.Columns.AutoFit();                                    // 设置列宽为自动适应   
            range.NumberFormatLocal = "#,##0.00";                       // 设置单元格格式为货币格式  
            range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //中间对齐

            range.Font.Bold = true;        // 加粗字体   
            range.Font.Size = 20;          // 设置字体大小
            range.RowHeight = 40;          //行高

            range.Value2 = title;
        }

        public void PrintSet(Worksheet sheet, Microsoft.Office.Interop.Excel.Application excel)      //打印设置   
        {
            sheet.PageSetup.Orientation = XlPageOrientation.xlPortrait; // 设置垂直版面   
            sheet.PageSetup.HeaderMargin = 0.0;                        // 设置页眉边距   
            sheet.PageSetup.FooterMargin = 0.0;                        // 设置页脚边距   
            sheet.PageSetup.LeftMargin = excel.InchesToPoints(0.354330708661417); // 设置左边距   
            sheet.PageSetup.RightMargin = excel.InchesToPoints(0.354330708661417);// 设置右边距   
            sheet.PageSetup.TopMargin = excel.InchesToPoints(0.393700787401575);  // 设置上边距   
            sheet.PageSetup.BottomMargin = excel.InchesToPoints(0.393700787401575);// 设置下边距   
            sheet.PageSetup.CenterHorizontally = true;                  // 设置水平居中   
        }   
        

        static string[] excelColTitle = {"A", "B", "C", "D", "E", "F", "G", 
                                            "H", "I", "J", "K", "L", "M", "N", 
                                            "O", "P", "Q", "R", "S", "T", "U", 
                                            "V", "W", "X", "Y", "Z",
                                            "AA", "AB", "AC", "AD", "AE", "AF", "AG", 
                                            "AH", "AI", "AJ", "AK", "AL", "AM", "AN", 
                                            "AO", "AP", "AQ", "AR", "AS", "AT", "AU", 
                                            "AV", "AW", "AX", "AY", "AZ" };
        
        private string path;

        private FormHousingDis.UpdateProgressBarDelegate updateFunc;
    }
}
